package com.cris.service;

import com.cris.helper.DatabaseHelper;
import com.cris.model.Chart;
import com.cris.model.Saleorder;
import org.springframework.stereotype.Service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by owen on 2017/5/18.
 */
@Service
public class ChartService {
    //获得一月份销售额
    public double getJanAmount(String year){
        double total=0;
        String sql="select * from saleorder where time>='"+year+"-01-01 00:00:00' and \n" +
                "time<'"+year+"-01-31 23:59:59'";
        List<Saleorder> list= DatabaseHelper.queryEntityList(Saleorder.class,sql);
        for (Saleorder sale:list){
            total+=sale.getTotal();
        }
        return total;
    }
    //获得二月份销售额
    public double getFebAmount(String year){
        double total=0;
        String sql="select * from saleorder where time>='"+year+"-02-01 00:00:00' and \n" +
                "time<'"+year+"-02-30 23:59:59'";
        List<Saleorder> list= DatabaseHelper.queryEntityList(Saleorder.class,sql);
        for (Saleorder sale:list){
            total+=sale.getTotal();
        }
        return total;
    }
    //获得三月份销售额
    public double getMarchAmount(String year){
        double total=0;
        String sql="select * from saleorder where time>='"+year+"-03-01 00:00:00' and \n" +
                "time<'"+year+"-03-31 23:59:59'";
        List<Saleorder> list= DatabaseHelper.queryEntityList(Saleorder.class,sql);
        for (Saleorder sale:list){
            total+=sale.getTotal();
        }
        return total;
    }
    //获得四月份销售额
    public double getAprilAmount(String year){
        double total=0;
        String sql="select * from saleorder where time>='"+year+"-04-01 00:00:00' and \n" +
                "time<'"+year+"-04-30 23:59:59'";
        List<Saleorder> list= DatabaseHelper.queryEntityList(Saleorder.class,sql);
        for (Saleorder sale:list){
            total+=sale.getTotal();
        }
        return total;
    }
    //获得五月份销售额
    public double getMayAmount(String year){
        double total=0;
        String sql="select * from saleorder where time>='"+year+"-05-01 00:00:00' and \n" +
                "time<'"+year+"-05-31 23:59:59'";
        List<Saleorder> list= DatabaseHelper.queryEntityList(Saleorder.class,sql);
        for (Saleorder sale:list){
            total+=sale.getTotal();
        }
        return total;
    }
    //获得六月份销售额
    public double getJulyAmount(String year){
        double total=0;
        String sql="select * from saleorder where time>='"+year+"-06-01 00:00:00' and \n" +
                "time<'"+year+"-06-30 23:59:59'";
        List<Saleorder> list= DatabaseHelper.queryEntityList(Saleorder.class,sql);
        for (Saleorder sale:list){
            total+=sale.getTotal();
        }
        return total;
    }
    //获得七月份销售额
    public double getJuneAmount(String year){
        double total=0;
        String sql="select * from saleorder where time>='"+year+"-07-01 00:00:00' and \n" +
                "time<'"+year+"-07-31 23:59:59'";
        List<Saleorder> list= DatabaseHelper.queryEntityList(Saleorder.class,sql);
        for (Saleorder sale:list){
            total+=sale.getTotal();
        }
        return total;
    }
    //获得八月份销售额
    public double getAugAmount(String year){
        double total=0;
        String sql="select * from saleorder where time>='"+year+"-08-01 00:00:00' and \n" +
                "time<'"+year+"-08-31 23:59:59'";
        List<Saleorder> list= DatabaseHelper.queryEntityList(Saleorder.class,sql);
        for (Saleorder sale:list){
            total+=sale.getTotal();
        }
        return total;
    }
    //获得九月份销售额
    public double getSeptAmount(String year){
        double total=0;
        String sql="select * from saleorder where time>='"+year+"-09-01 00:00:00' and \n" +
                "time<'"+year+"-09-30 23:59:59'";
        List<Saleorder> list= DatabaseHelper.queryEntityList(Saleorder.class,sql);
        for (Saleorder sale:list){
            total+=sale.getTotal();
        }
        return total;
    }
    //获得十月份销售额
    public double getOctAmount(String year){
        double total=0;
        String sql="select * from saleorder where time>='"+year+"-10-01 00:00:00' and \n" +
                "time<'"+year+"-10-31 23:59:59'";
        List<Saleorder> list= DatabaseHelper.queryEntityList(Saleorder.class,sql);
        for (Saleorder sale:list){
            total+=sale.getTotal();
        }
        return total;
    }
    //获得十一月份销售额
    public double getNovAmount(String year){
        double total=0;
        String sql="select * from saleorder where time>='"+year+"-11-01 00:00:00' and \n" +
                "time<'"+year+"-11-30 23:59:59'";
        List<Saleorder> list= DatabaseHelper.queryEntityList(Saleorder.class,sql);
        for (Saleorder sale:list){
            total+=sale.getTotal();
        }
        return total;
    }
    //获得十二月份销售额
    public double getDecAmount(String year){
        double total=0;
        String sql="select * from saleorder where time>='"+year+"-12-01 00:00:00' and \n" +
                "time<'"+year+"-12-31 23:59:59'";
        List<Saleorder> list= DatabaseHelper.queryEntityList(Saleorder.class,sql);
        for (Saleorder sale:list){
            total+=sale.getTotal();
        }
        return total;
    }

    //按起止时间获得产品类型销售额
    public Map<String,Double> getAmountBetween(String from, String to){
        //并表查询
        //从orderitem表和saleorder表和product表中查询得某种产品类型 在某个时间段内得销售额
        String sql = "select producttype.name,aa.totals from producttype,(SELECT parentid,sum(total) as totals FROM saleorder,product,producttype,orderitem where saleorder.orderno=orderitem.orderno and orderitem.id=product.id and product.typeid=producttype.typeid  and saleorder.time>='"+from+" 00:00:00' and saleorder.time<'"+to+"00:00:00' group by parentid)AS aa  where aa.parentid=producttype.typeid";
        List<Chart> list = DatabaseHelper.queryEntityList(Chart.class,sql);
        System.out.println(list);
        Map<String,Double> map = new HashMap<>();
        for(Chart chart:list){
            map.put(chart.getName(),chart.getTotals());
        }
        return map;
    }


}
